package com.xrh.core.generater.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.xrh.core.bean.OrderByInfo;
import com.xrh.core.bean.Queryer;
import com.xrh.core.bean.SQLQueryerParser;
import com.xrh.core.generater.bean.ColumnBean;
import com.xrh.core.generater.bean.DataBaseConfig;
import com.xrh.core.generater.bean.TableInfo;
import com.xrh.core.utils.PagingUtils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @function 数据库工具类
 * @author ronghui.xiao
 * @date 2017年6月24日
 */
@Slf4j
public class OracleDataBaseUtils {
	
	
	public static void main(String[] args) throws Exception {
		
		DataBaseConfig dbBaseConfig = DataBaseConfig.createOracleConfig("127.0.0.1" , "1521" ,  "WXHIS" , "WXHIS" , "123456" , 1 , "XE");
			
		//查看表和视图
//		List<String> list = OracleDataBaseUtils.getTableNames(dbBaseConfig);
//		
//		System.out.println(JSONUtil.toJsonPrettyStr(list));
		
		//查看表DDL
//		String ddl = OracleDataBaseUtils.getCreateTableDDL(dbBaseConfig , "SY_GCP_ASSAY_VIEW");
		
		
//		System.out.println(ddl);
		
		//查看所有的数据库
//		 List<String> dbNameList = OracleDataBaseUtils.getDbNameList(dbBaseConfig);
//		 
//		 System.out.println(JSONUtil.toJsonPrettyStr(dbNameList));
		 
//		 String sql = "select * from MY_VIEW";
//		 
//		 //执行查询
//		 sql = "select * from (select  tab.*, rownum as rowno from (	" + sql + ") tab where rownum <= " + (1 * 10) + " ) where rowno > " + ((1 - 1) * 10);
//
//		 List<LinkedHashMap<String , Object>> dataList = OracleDataBaseUtils.executeQuerySQL(dbBaseConfig, sql);
//
//		 System.out.println(JSONUtil.toJsonPrettyStr(dataList));
		
        Queryer queryer = new Queryer();
        
//        queryer.orderBy(OrderByInfo.asc("院区") , OrderByInfo.desc("单价"));
        
        
        queryer.eq("项目编码", "F10000110547");
		
        List<LinkedHashMap<String , Object>> dataList = findRecordListByQueryer(dbBaseConfig, "WXHIS.V_GCP_DIC_UNDRUG", queryer, 1, 10);
		
		System.out.println(JSONUtil.toJsonPrettyStr(dataList));
	}
	
	/**
	 * @function 获取数据库名称列表
	 * @param dataBaseConfig
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static  List<String> getDbNameList(DataBaseConfig dataBaseConfig) throws Exception{
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		ResultSet resultSet = databaseMetaData.getCatalogs();
		
		List<String> dbNameList = new ArrayList<String>();
		
		while (resultSet.next()) {
			dbNameList.add(StrUtil.trim(resultSet.getString("TABLE_CAT")));
		}
		
		conn.close();
		
		
		//oracle只能查询自己的 , 能链接
		if(dataBaseConfig.getDbTypeFlag() == 1 && CollUtil.isEmpty(dbNameList)) 
			return CollUtil.newArrayList(dataBaseConfig.getDataBaseName());
		
		return dbNameList;
	}
	
	/**
	 * @function 获取创建表的DDL语句
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static String getCreateTableDDL(DataBaseConfig dataBaseConfig , String tableName) throws Exception {
		
		String sql = "show create table " + tableName;
		
		//若是oracle
		if(dataBaseConfig.getDbTypeFlag() == 1)
			sql = "SELECT DBMS_METADATA.GET_DDL('TABLE','"+tableName+"' , '"+dataBaseConfig.getDataBaseName()+"') FROM DUAL";
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
	    
	    ResultSet rs = statement.executeQuery(sql);
		
	    String ddl = "";
	    
		while (rs.next()) {
			ddl = dataBaseConfig.getDbTypeFlag() == 1 ? rs.getString(1) : rs.getString(2);
			break;
		}
		
		conn.close();
		
		return ddl;
		
	}
	
	/**
	 * @function 获取数据库所有表的名称集合
	 * @author ronghui.xiao
	 * @date 2017年6月24日
	 */
	public static List<String> getTableNames(DataBaseConfig dataBaseConfig) throws Exception{
		
		List<String> list = new ArrayList<String>();
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);

		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		///*其中"%"就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用"%"来作为参数了。*/
		ResultSet tableRet = databaseMetaData.getTables(conn.getCatalog(), dataBaseConfig.getDataBaseName()  , null ,  new String[]{"TABLE","VIEW"}); 
		
		while(tableRet.next()){
			list.add(tableRet.getString("TABLE_NAME"));
		}
		
		conn.close();
		
		return list;
	}
	
	/**
	 * @function 分页查询某个表的记录集合
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static List<LinkedHashMap<String , Object>> findRecordListByTableName(DataBaseConfig dataBaseConfig , String tableName , int pageNum , int pageSize)  throws Exception {
		
	    String sql = "select * from " + tableName +  " limit " + PagingUtils.getOffset(pageNum, pageSize) + " , " + pageSize;
		
	    //oracle 的查询要单独处理下
	    if(dataBaseConfig.getDbTypeFlag() == 1) {
	    	
	    	sql = "select * from " + tableName;
	    	
	    	sql = "select * from (select  tab.*, rownum as rowno from (	" + sql + ") tab where rownum <= " + (pageNum * pageSize) + " ) where rowno > " + ((pageNum - 1) * pageSize);
	    }
	    
	    return executeQuerySQL(dataBaseConfig , sql);
	}
	
	/**
     * @function 创建查询SQL
     * @author 肖荣辉
     * @date 2023年1月4日
     */
    public static String createQuerySql(Queryer queryer) {
    	
    	if(queryer == null)
    		return "";
    	
    	String querySql = queryer.parseQueryer(new SQLQueryerParser());
    	
    	querySql = StrUtil.isBlank(querySql) ? "" : " where " + querySql;
    	
    	return querySql;
    }
    
	/**
	 * @function 分页查询某个表的其中一条记录
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static <T> T findOneRecordByQueryer(Class<T> clazz , DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer)  throws Exception {
		
		List<LinkedHashMap<String , Object>> list = findRecordListByQueryer(dataBaseConfig , tableName , queryer , 1 , 1);
		
		return CollUtil.isEmpty(list) ? null : BeanUtil.copyProperties(list.get(0), clazz);
	}
    
	/**
	 * @function 分页查询某个表的记录集合
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static <T> List<T> findRecordListByQueryer(Class<T> clazz , DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer , int pageNum , int pageSize)  throws Exception {
		
		List<LinkedHashMap<String , Object>> list = findRecordListByQueryer(dataBaseConfig, tableName, queryer, pageNum, pageSize);
		
		return CollUtil.isEmpty(list) ? new ArrayList<T>() : BeanUtil.copyToList(list , clazz);
		
	}
	
	/**
	 * @function 分页查询某个表的记录集合
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static List<LinkedHashMap<String , Object>> findRecordListByQueryer(DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer , int pageNum , int pageSize)  throws Exception {
		
		String querySql = createQuerySql(queryer);
		
		//要查询的字段
		String selectColumnTxt = CollUtil.isNotEmpty(queryer.getSelectColumnNames()) ? StrUtil.join(",", queryer.getSelectColumnNames()) : "*";
		
	    //oracle 的查询要单独处理下
	    String sql = "select "+selectColumnTxt+"  from " + tableName + querySql;
	    
	    //分组字段 , 分组要放到order by前面
		if(CollUtil.isNotEmpty(queryer.getGroupByColumnNames())) {
			
			String groupByTxt = " group by ";
			
			groupByTxt +=  StrUtil.join(",", queryer.getGroupByColumnNames());
			
			sql += groupByTxt;
		}
		
	    //having表达式
		if(CollUtil.isNotEmpty(queryer.getHavingExpressions())) {
			
			String havingTxt = " having ";
			
			havingTxt +=  StrUtil.join(",", queryer.getHavingExpressions());
			
			sql += havingTxt;
		}
	    
	    //每个字段按照不同的方式进行排序
		if(CollUtil.isNotEmpty(queryer.getOrderByList())) {
			
			String orderByTxt = " order by ";
			
			List<String> tempList = new ArrayList<String>();
			
			for(OrderByInfo orderByInfo : queryer.getOrderByList()) {
				tempList.add(orderByInfo.getColumnName() + " " + (orderByInfo.isOrderByAsc() ? "asc" : "desc") );
			}
			
			orderByTxt +=  StrUtil.join(",", tempList);
			
			sql += orderByTxt;
			
		}
	    	
	    sql = "select * from (select  tab.*, rownum as rowno from (	" + sql + ") tab where rownum <= " + (pageNum * pageSize) + " ) where rowno > " + ((pageNum - 1) * pageSize);
	    
	    return executeQuerySQL(dataBaseConfig , sql);
	}
	
	/**
	 * @function 分页查询某个表的记录总条数
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static long findRecordListCountByQueryer(DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer)  throws Exception {
		
        long count = 0;

        //获取表名
        Connection conn = getConnection(dataBaseConfig);

        Statement statement = conn.createStatement();

        String sql = "select count(*) COUNT from " + tableName + createQuerySql(queryer);
        
        log.info("执行统计SQl : {}" , sql);
        
        ResultSet  rs = statement.executeQuery(sql);

        while (rs != null && rs.next()) {
            count = rs.getLong("COUNT");
            break;
        }
        
        conn.close();
            
        return count;
	}
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public  static <T>  List<T>  executeQuerySQL(Class<T> clazz  , DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		List<LinkedHashMap<String , Object>> list = executeQuerySQL(dataBaseConfig, sql);
		
		return CollUtil.isEmpty(list) ? new ArrayList<T>() : BeanUtil.copyToList(list , clazz);
	}
		
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static  List<LinkedHashMap<String , Object>>  executeQuerySQL(DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		log.info("执行查询SQL : \n{}" , sql );
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
	    Statement statement = conn.createStatement();
	    
	    ResultSet rs = statement.executeQuery(sql);
	    
	    List<String> columnNameList = new ArrayList<String>();
	    
	    int size = rs.getMetaData().getColumnCount();
	    
	    ResultSetMetaData metaData = rs.getMetaData();
	    
	    Map<String , String> columnNameTypeMap = new HashMap<String, String>();
	    
	    for(int i = 1 ; i <= size ; i ++) {
	    	
	    	String columnName = metaData.getColumnLabel(i);
	    	
	    	columnNameTypeMap.put(columnName , metaData.getColumnTypeName(i));
	    	
	    	columnNameList.add(columnName);
	    }
	    
	    List<LinkedHashMap<String , Object>> recordList = new ArrayList<LinkedHashMap<String,Object>>();
	    
        while (rs != null && rs.next()) {
        	
    	    LinkedHashMap<String , Object> record = new LinkedHashMap<String, Object>();
    	    
        	for(String columnName: columnNameList) {
        		
        		Object object = rs.getObject(columnName);
        		
        		String type = columnNameTypeMap.get(columnName);
        		
        		if("TIMESTAMP".equals(type) && object != null )
        			object = DateUtil.formatDateTime(Convert.toDate(object));
        		
        		if("DATE".equals(type) && object != null )
        			object = DateUtil.formatDateTime(Convert.toDate(object));
        		
        		if("DATETIME".equals(type) && object != null )
        			object = DateUtil.formatDateTime(Convert.toDate(object));
        		
        		record.put(columnName , object);
        	}
        	
        	recordList.add(record);

        }
	    
	   conn.close();
	   
	   return recordList;
	}
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2022年9月16日
	*/
	public static  int  executeUpdateSQL(DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		log.info("执行更新SQL : \n{}" , sql );
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
	    Statement statement = conn.createStatement();
	    
	    int count = statement.executeUpdate(sql);
	    
	   conn.close();
	   
	   return count;
	}
	
	/**
	* @function 获取表中的主键
	* @author 肖荣辉
	 * @throws SQLException 
	* @date 2018年11月8日
	*/
	private static String getPrimaryKey(DataBaseConfig dataBaseConfig , String tableName) throws Exception{
		
		String sql = "select column_name from user_cons_columns u,user_constraints c where u.table_name = '"+tableName+"' and u.constraint_name = c.index_name and c.constraint_type ='P'";
		
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
		
	    ResultSet rs = statement.executeQuery(sql);
	    
	    while (rs != null && rs.next()) {
	    	return rs.getString("column_name");
        }
         
       rs.close();
       conn.close();
        
       return "";
	}
	
	/**
	 * @function 获取字段名和注释映射
	 * @author 肖荣辉
	 * @date 2020年12月10日
	*/
	public static Map<String,String> getColumnCommentMap(DataBaseConfig dataBaseConfig , String tableName) throws Exception{
		
		Map<String , String> map = new HashMap<String, String>();
		
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
		
		String sql = "select * from user_col_comments where table_name ='"+tableName+"'";
		
		ResultSet rs = statement.executeQuery(sql);
		
		while (rs != null && rs.next()) {
			String name = rs.getString("column_name");
			String comments = rs.getString("comments");
			map.put(name , comments == null ? "" : comments);
		}
		
		rs.close();
		conn.close();
		
		return map;
	}
	
	/**
	 * @function 根据数据库配置信息获取表信息map
	 * @author ronghui.xiao
	 * @throws Exception 
	 * @date 2017年6月24日
	 */
	public static Map<String , TableInfo> getTableInfoMap(DataBaseConfig dataBaseConfig) throws Exception{
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
		System.out.println("正在读取数据库元信息...");
		
		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		System.out.println("读取数据库元信息成功!");
		
		ResultSet resultSet = null;
		
		Map<String , TableInfo> tableInfoMap = new HashMap<String, TableInfo>();
		
		try {
			
			System.out.println("正在读取数据库 : ["+ dataBaseConfig.getDataBaseName() +"]所有的表...");
			resultSet = databaseMetaData.getTables(conn.getCatalog() ,dataBaseConfig.getDataBaseName() , null, new String[] { "TABLE" , "VIEW" });
			System.out.println("读取数据库 : ["+dataBaseConfig.getDataBaseName() +"]所有的表成功!");
			
			
			while (resultSet.next()) {
				
				String name = resultSet.getString("TABLE_NAME");
				String comment = getCommentByTableName(dataBaseConfig , name);
				
				System.out.println("读取表==> [" + name + " : " + comment + "]的相关信息" );
				
				TableInfo tableInfo = new TableInfo(name , comment);
				tableInfo.setPrimaryKeys(Arrays.asList(getPrimaryKey(dataBaseConfig , name)));
				
				Map<String , String> columnCommentMap = getColumnCommentMap(dataBaseConfig , name);
				
				LinkedHashMap<String , ColumnBean> columnInfoMap = getColumnInfoMap(dataBaseConfig , tableInfo , columnCommentMap);
				tableInfo.setColumnInfoMap(columnInfoMap);
				
				tableInfoMap.put(name, tableInfo);
				
			}
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		conn.close();
		
		return tableInfoMap;
		
	}
	
	/**
	 * @function 获取某个表的结构信息
	 * @author ronghui.xiao
	 * @throws Exception 
	 * @date 2017年6月24日
	 */
	public static TableInfo getTableInfo(DataBaseConfig dataBaseConfig , String tableName) throws Exception{
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
		System.out.println("正在读取数据库元信息...");
		
		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		System.out.println("读取数据库元信息成功!");
		
		ResultSet resultSet = null;
		
		TableInfo tableInfo = null;
		
		try {
			
			System.out.println("正在读取数据库 : ["+ dataBaseConfig.getDataBaseName() +"]查询表 : "+tableName);
			
			resultSet = databaseMetaData.getTables(conn.getCatalog() ,dataBaseConfig.getDataBaseName() , tableName , new String[] { "TABLE" , "VIEW" });
			
			while (resultSet.next()) {
				
				String name = resultSet.getString("TABLE_NAME");
				
				if(!tableName.equals(name)) continue;
				
				String comment = getCommentByTableName(dataBaseConfig , name);
				
				System.out.println("读取表==> [" + name + " : " + comment + "]的相关信息" );
				
				tableInfo = new TableInfo(name , comment);
				
				tableInfo.setPrimaryKeys(Arrays.asList(getPrimaryKey(dataBaseConfig , name)));
				
				Map<String , String> columnCommentMap = getColumnCommentMap(dataBaseConfig , name);
				
				LinkedHashMap<String , ColumnBean> columnInfoMap = getColumnInfoMap(dataBaseConfig , tableInfo , columnCommentMap);
				
				tableInfo.setColumnInfoMap(columnInfoMap);
				
			}
		
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			conn.close();
		}

		conn.close();
		
		return tableInfo;
		
	}
	
	
	//获取表名和键值对的集合
	public static LinkedHashMap<String , ColumnBean>  getColumnInfoMap(DataBaseConfig dataBaseConfig , TableInfo tableInfo , Map<String , String> columnCommentMap) throws Exception {
		
		String tableName = tableInfo.getTableName();
		
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
		
		String sql = "select table_name, column_name, data_type , data_scale , data_length from all_tab_columns where table_name='"+tableName+"'";
		  
		ResultSet rs = statement.executeQuery(sql);
		
		LinkedHashMap<String , ColumnBean> map = new LinkedHashMap<String, ColumnBean>();
		while (rs != null && rs.next()) {
			String column_name = rs.getString("column_name");
			String data_type = rs.getString("data_type");
			int data_scale = rs.getInt("data_scale");
			int data_length = rs.getInt("data_length");
			
			String comment = columnCommentMap.get(column_name); 
			ColumnBean columnBean = new ColumnBean(tableInfo , column_name, data_length, comment , data_type , data_scale);
			map.put(column_name, columnBean);
		}
	
		rs.close();
		conn.close();
		
		tableInfo.setColumnInfoMap(map);
		
		return map;
		
	}
	
	//获取表名和键值对的集合
	public static String  getCommentByTableName(DataBaseConfig dataBaseConfig , String tableName) throws Exception {
       
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
		
	    ResultSet rs = statement.executeQuery("select * from user_tab_comments where Table_Name='"+tableName+"'");
	    
	    String comment = null;
	    
        if (rs != null && rs.next()) {
    		comment = rs.getString(3);
        }
         
       rs.close();
       conn.close();
        
       return comment == null ? "" : comment;
        
    }
	
	/**
	 * @function 获取链接
	 * @author ronghui.xiao
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @date 2017年6月24日
	 */
	public static Connection getConnection(DataBaseConfig config) throws ClassNotFoundException, SQLException {
		
		String driver = config.getDriver();
	    String url = config.getJdbcUrl();
	    String username = config.getDbUserName();
	    String password = config.getPassword();
	    
	    Connection conn = null;
        
	    Class.forName(driver); 
        
	    conn = (Connection) DriverManager.getConnection(url, username, password);
	    
        return conn;
	}
	
}
